package servlet;

import java.io.IOException;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import model.College;
import model.Major;
import model.Student;
import util.DBHelper;
import util.StuUtil;
//学生业务控制类
public class StudentServlet extends BaseServlet {
	//添加学生信息
	public void addOneStudent(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {
		Student s = new Student();
		s.getParams(req);
		//组装sql
		String sql = s.getInsertSql();
//		System.out.println(sql);
		int count = DBHelper.execute(sql, null);
		
		PrintWriter out = res.getWriter();
		if (count > 0) {
			out.write("1");
		}else {
			out.write("0");
		}
		out.flush();
		out.close();
	}
	//跳转到更新学生页面
	public void updateStudentPage(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {
		Student s = new Student();
		s.getParams(req);
		//查询所有专业
		String sql3 = "select * from major";
		List<Major> allMajors = DBHelper.query(sql3, Major.class, null);
		req.setAttribute("allMajors", allMajors);//专业列表

		//查询所有学院
		String sql2 = "select * from college";
		List<College> allColleges = DBHelper.query(sql2, College.class, null);
		//组装sql
		String sql = s.getSelectSql();
//		System.out.println(sql);
		List<Student> list = DBHelper.query(sql, Student.class, null);
		
		req.setAttribute("obj", list.get(0));
		req.setAttribute("allColleges", allColleges);

		req.getRequestDispatcher("update_stu.jsp").forward(req, res);
	}	
	//跳转到列表页
	public void selectAllStudent(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {
		Student s = new Student();
		s.getParams(req);
		//查询所有年级
		String sql1 = "select s_grade from student group by s_grade";
		List<Object> allGrades = DBHelper.query(sql1);
		req.setAttribute("allGrades", allGrades);//年级列表
		//查询所有学院
		String sql2 = "select * from college";
		List<College> allColleges = DBHelper.query(sql2, College.class, null);
		req.setAttribute("allColleges", allColleges);//学院列表
		//查询所有专业
		String sql3 = "select * from major";
		List<Major> allMajors = DBHelper.query(sql3, Major.class, null);
		req.setAttribute("allMajors", allMajors);//专业列表
		//计算总行数
		String totalCountSql = s.getTotalCountSql();
		long totalCount = (long)DBHelper.query(totalCountSql).get(0);
		
		String sql = s.getSelectSql();
//		System.out.println(sql);
		if (s.getsGrade() != null && !s.getsGrade().equals("")){
            req.setAttribute("grade",s.getsGrade());
        }
		if (s.getcId() != null && !s.getcId().equals("")){
            req.setAttribute("cId",s.getcId());
        }
		if (s.getmId() != null && !s.getmId().equals("")){
            req.setAttribute("mId",s.getmId());
        }
		//计算分页起始行
		int limitPageNo = (s.getPageNo() - 1) * s.getRowCount();
		sql += " limit " + limitPageNo + "," + s.getRowCount();
		List<Student> list = DBHelper.query(sql, Student.class, null);

		for (Student student : list) {
			if (student.getsSex()==0) {
				student.setSex("女");
			}else if(student.getsSex()==1) {
				student.setSex("男");
			}
			//企业类型:1-私企 2-国企 3-外企 4-集体企业 5-事业单位 6-科研机构 0-待业
			if (student.getComType()==0) {
				student.setComtype("待业");
			}else if (student.getComType()==1) {
				student.setComtype("私企");
			}else if (student.getComType()==2) {
				student.setComtype("国企");
			}else if (student.getComType()==3) {
				student.setComtype("外企");
			}else if (student.getComType()==4) {
				student.setComtype("集体企业");
			}else if (student.getComType()==5) {
				student.setComtype("事业单位");
			}else if (student.getComType()==6) {
				student.setComtype("科研机构");
			}
		}
		
		req.setAttribute("list", list);
		
		//添加查询条件参数
		req.setAttribute("sName", s.getsName());
		req.setAttribute("sGrade", s.getsGrade());
		req.setAttribute("cId", s.getcId());
		req.setAttribute("mId", s.getmId());
		
		//添加分页参数
		req.setAttribute("pageNo", s.getPageNo());
		//计算总页数
		int totalPage = (int)(totalCount / s.getRowCount());
		if (totalCount % s.getRowCount() != 0 ) {
			totalPage += 1;
		}
//		System.out.println(totalPage);
		req.setAttribute("totalPage", totalPage);
		req.getRequestDispatcher("stu_mana.jsp").forward(req, res);
	}
	//跳转添加学生页面
	public void addStudentPage(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {
	
		//查询所有学院
		String sql = "select * from college";
		List<College> allColleges = DBHelper.query(sql, College.class, null);

		req.setAttribute("allColleges", allColleges);
		req.getRequestDispatcher("add_stu.jsp").forward(req, res);
	}	
	//更新学生细信息
	public void updateStudent(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {
//		System.out.println(11);
		Student s = new Student();
//		System.out.println(22);
		s.getParams(req);
//		System.out.println(33);
		String sql = s.getUpdateSql();
		int count = DBHelper.execute(sql, null);

		PrintWriter out = res.getWriter();
		if (count > 0) {
			out.write("1");
		}else {
			out.write("0");
		}
		out.flush();
		out.close();
	}
	//导出表格
	public void download(HttpServletRequest req, HttpServletResponse res) {
		//工作台，也可以就是一个excel表格对象
		XSSFWorkbook wb = new XSSFWorkbook();
		/*
		 *XSSF可以兼容office07以后的版本
		 *HSSF只能兼容 office07以前的版本
		 */
		//创建一个sheet页对象
		XSSFSheet sheet = wb.createSheet();
		
		//第一页
		XSSFRow row = sheet.createRow(0);
		
		//设置粗体样式
		CellStyle style = wb.createCellStyle();//create style
		Font font = wb.createFont();//create font
		font.setBold(true);//字体加粗
		style.setFont(font);
		//设置第一行标题
		row.createCell(0, CellType.STRING).setCellValue("姓名");
		row.createCell(1, CellType.STRING).setCellValue("性别");
		row.createCell(2, CellType.STRING).setCellValue("年级");
		row.createCell(3, CellType.STRING).setCellValue("学院");
		row.createCell(4, CellType.STRING).setCellValue("专业");
		row.createCell(5, CellType.STRING).setCellValue("班级");
		row.createCell(6, CellType.STRING).setCellValue("就业单位");
		row.createCell(7, CellType.STRING).setCellValue("就业城市");
		row.createCell(8, CellType.STRING).setCellValue("岗位");
		row.createCell(9, CellType.STRING).setCellValue("企业类型");
		row.createCell(10, CellType.STRING).setCellValue("薪资");	
		
//		XSSFCell cell = row.createCell(11, CellType.STRING);
//		cell.setCellValue("123");
//		cell.setCellStyle(style);
		//设置第一行全部粗体样式
		for (Cell cell : row) {
			cell.setCellStyle(style);
		}
		//查询学生列表
		List<Student> list = DBHelper.query(new Student().getSelectSql(), Student.class, null);
		for (Student student : list) {
			if (student.getsSex()==0) {
				student.setSex("女");
			}else if(student.getsSex()==1) {
				student.setSex("男");
			}
			//企业类型:1-私企 2-国企 3-外企 4-集体企业 5-事业单位 6-科研机构 0-待业
			if (student.getComType()==0) {
				student.setComtype("待业");
			}else if (student.getComType()==1) {
				student.setComtype("私企");
			}else if (student.getComType()==2) {
				student.setComtype("国企");
			}else if (student.getComType()==3) {
				student.setComtype("外企");
			}else if (student.getComType()==4) {
				student.setComtype("集体企业");
			}else if (student.getComType()==5) {
				student.setComtype("事业单位");
			}else if (student.getComType()==6) {
				student.setComtype("科研机构");
			}
		}
		for (int i = 0; i < list.size(); i++) {

			XSSFRow nextRow = sheet.createRow(i+1);
			Student s = list.get(i);
			
			dealCellValue(nextRow, 0, s.getsName());
			dealCellValue(nextRow, 1, s.getSex());
			dealCellValue(nextRow, 2, s.getsGrade());
			dealCellValue(nextRow, 3, s.getcName());
			dealCellValue(nextRow, 4, s.getmName());
			dealCellValue(nextRow, 5, s.getClazz());
			dealCellValue(nextRow, 6, s.getEmpCompany());
			dealCellValue(nextRow, 7, s.getEmpCity());
			dealCellValue(nextRow, 8, s.getEmpStation());
			dealCellValue(nextRow, 9, s.getComtype());
			dealCellValue(nextRow, 10, s.getSalary());
		}
		//设置浏览器响应格式
		res.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
		String fileName;//表格文件名
		try {
			fileName = URLEncoder.encode("用户信息", "UTF-8");
			//设置响应的头部信息，添加文件名头部信息
			res.setHeader("Content-Disposition", "attachment;filename="+fileName+".xls");
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}
		try {
			//创建输出流
			OutputStream out = res.getOutputStream();
			wb.write(out);
			out.flush();
			out.close();
			wb.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	//给单元格赋值
	public void dealCellValue(XSSFRow row,int index,Object v) {
		row.createCell(index, CellType.STRING).setCellValue(StuUtil.getV(v));
	}
}
